//
//  DataBase.m
//  BookSale
//
//  Created by dlios on 15-3-10.
//  Copyright (c) 2015年 dlios. All rights reserved.
//

#import "DataBase.h"

@implementation DataBase
+ (instancetype)shareDataBaseHandle
{
    static DataBase *dataBaseHandle = nil;
    if (dataBaseHandle == nil) {
        dataBaseHandle = [[DataBase alloc] init];
    }
    return dataBaseHandle;
}
static sqlite3 *db;
//打开数据库
- (void)openDB
{
    //获取沙盒路径
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentFilePath = [array lastObject];
    NSString *sqlitePath = [documentFilePath stringByAppendingPathComponent:@"bookSale.sqlite"];
    NSLog(@"数据路路径=%@", sqlitePath);
    //打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else
    {
        NSLog(@"数据库打开失败");
    }
    
}

//创建总数据表
- (void)createAllTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS AllTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向总数据表中插入内容
- (void)insertAll:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO AllTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}



//查询总数据
- (NSMutableArray *)selectAll
{
    NSString *selectSql = @"SELECT * FROM AllTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}


//创建我的收藏表
- (void)createCollectionTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS CollectionTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向我的收藏表中插入内容
- (void)insertCollection:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO CollectionTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

// 更新我得收藏
- (void)updateCollection:(YoungBook *)youngBook number:(NSInteger)number
{
    //1.创建更新学生的sql语句
    NSString *updateSql = [NSString stringWithFormat:@"UPDATE CollectionTable SET name = '%@', subname = '%@', productId = '%@', image_url = '%@', authorname = '%@', dd_sale_price = '%@', original_price = '%@' WHERE number = '%ld'", youngBook.name, youngBook.subname, youngBook.productId, youngBook.image_url, youngBook.authorname, youngBook.dd_sale_price, youngBook.original_price, number];
    //2.执行sql语句
    int result = sqlite3_exec(db, updateSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"更改成功");
    } else {
        NSLog(@"更改失败");
    }
    
}

//删除我的收藏
- (void)deleteCollection:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM CollectionTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询我的收藏
- (NSMutableArray *)selectCollection
{
    NSString *selectSql = @"SELECT * FROM CollectionTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}


//创建青春文学表
- (void)createYoungTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS YoungTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向青春文学表中插入内容
- (void)insertYoungBook:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO YoungTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

// 更新青春文学
- (void)updateYoungBook:(YoungBook *)youngBook number:(NSInteger)number
{
    //1.创建更新学生的sql语句
    NSString *updateSql = [NSString stringWithFormat:@"UPDATE YoungTable SET name = '%@', subname = '%@', productId = '%@', image_url = '%@', authorname = '%@', dd_sale_price = '%@', original_price = '%@' WHERE number = '%ld'", youngBook.name, youngBook.subname, youngBook.productId, youngBook.image_url, youngBook.authorname, youngBook.dd_sale_price, youngBook.original_price, number];
    //2.执行sql语句
    int result = sqlite3_exec(db, updateSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"更改成功");
    } else {
        NSLog(@"更改失败");
    }
    
}

//删除青春文学
- (void)deleteYoungBook:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM YoungTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询青春文学
- (NSMutableArray *)selectYoungBook
{
       NSString *selectSql = @"SELECT * FROM YoungTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}

//创建小说表
- (void)createNovelTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS NovelTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向小说表中插入内容
- (void)insertNovel:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO NovelTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

//删除小说
- (void)deleteNovel:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM NovelTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询小说
- (NSMutableArray *)selectNovel
{
    NSString *selectSql = @"SELECT * FROM NovelTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}

//创建科普表
- (void)createScienceTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS ScienceTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向科普表中插入内容
- (void)insertScience:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO ScienceTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

//删除科普
- (void)deleteScience:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM ScienceTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询科普
- (NSMutableArray *)selectScience
{
    NSString *selectSql = @"SELECT * FROM ScienceTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}

//创建社科表
- (void)createSocialTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS SocialTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向社科表中插入内容
- (void)insertSocial:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO SocialTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

//删除社科
- (void)deleteSocial:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM SocialTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询社科
- (NSMutableArray *)selectSocial
{
    NSString *selectSql = @"SELECT * FROM SocialTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}

//创建管理表
- (void)createManagerTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS ManagerTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向管理表中插入内容
- (void)insertManager:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO ManagerTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

//删除管理
- (void)deleteManager:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM ManagerTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询管理
- (NSMutableArray *)selectManager
{
    NSString *selectSql = @"SELECT * FROM ManagerTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}

//创建文学表
- (void)createLiteratureTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS LiteratureTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向文学表中插入内容
- (void)insertLiterature:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO LiteratureTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

//删除文学
- (void)deleteLiterature:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM LiteratureTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询文学
- (NSMutableArray *)selectLiterature
{
    NSString *selectSql = @"SELECT * FROM LiteratureTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}

//创建生活表
- (void)createLifeTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS LifeTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向生活表中插入内容
- (void)insertLife:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO LifeTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

//删除生活
- (void)deleteLife:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM LifeTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询生活
- (NSMutableArray *)selectLife
{
    NSString *selectSql = @"SELECT * FROM LifeTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}

//创建生活表
- (void)createArtTable
{
    NSString *createTableSql = @"CREATE TABLE IF NOT EXISTS ArtTable (number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, authorname TEXT, subname TEXT, productId TEXT, image_url TEXT, original_price TEXT, dd_sale_price TEXT)";
    int result = sqlite3_exec(db, createTableSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else
    {
        NSLog(@"创建表失败");
    }
}
//向艺术表中插入内容
- (void)insertArt:(YoungBook *)book number:(NSInteger)number
{
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO ArtTable (number, name, authorname, subname, productId, image_url, original_price, dd_sale_price)Values(%ld, '%@', '%@', '%@', '%@', '%@', '%@', '%@')", number, book.name, book.authorname, book.subname, book.productId, book.image_url, book.original_price, book.dd_sale_price];
    int result = sqlite3_exec(db, insertSql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入表成功");
    }else
    {
        NSLog(@"插入表失败");
    }
}

//删除艺术
- (void)deleteArt:(NSInteger)number
{
    NSString *deleteSql = [NSString stringWithFormat:@"DELETE FROM ArtTable WHERE number = '%ld'", number];
    //执行
    int result = sqlite3_exec(db, deleteSql.UTF8String, NULL, NULL, nil);
    if (SQLITE_OK == result) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
    
    
}

//查询艺术
- (NSMutableArray *)selectArt
{
    NSString *selectSql = @"SELECT * FROM ArtTable";
    sqlite3_stmt *stmt = nil;
    //sqlite3_prepare_v2其实就是将数据库对象sql语句,跟随指针三者关联到了一起,来共同完成查询操作
    int result = sqlite3_prepare_v2(db, selectSql.UTF8String, -1, &stmt, nil);
    
    //创建存放学生对象的大数组
    NSMutableArray *YoungArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        
        //开始循环遍历查询数据库每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //当跟随指针指向的行的数据符合你的查询条件,就在while返回
            //逐行取出每一列数据
            // int number = sqlite3_column_int(stmt, 0);
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *authorname = sqlite3_column_text(stmt, 2);
            const unsigned char *subname = sqlite3_column_text(stmt, 3);
            const unsigned char *productId = sqlite3_column_text(stmt, 4);
            const unsigned char *image_url = sqlite3_column_text(stmt, 5);
            const unsigned char *dd_sale_price = sqlite3_column_text(stmt, 6);
            const unsigned char *original_price = sqlite3_column_text(stmt, 7);
            //转成字符串
            NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
            NSString *authornameStr = [NSString stringWithUTF8String:(const char *)authorname];
            NSString *subnameStr = [NSString stringWithUTF8String:(const char *)subname];
            NSString *productIdStr = [NSString stringWithUTF8String:(const char *)productId];
            NSString *image_urlStr = [NSString stringWithUTF8String:(const char *)image_url];
            NSString *dd_sale_priceStr = [NSString stringWithUTF8String:(const char *)dd_sale_price];
            NSString *original_priceStr = [NSString stringWithUTF8String:(const char *)original_price];
            //NSString *headPhotoStr = (NSString *)data;
            
            
            YoungBook *book = [[YoungBook alloc] init];
            book.name = nameStr;
            book.authorname = authornameStr;
            book.subname = subnameStr;
            book.productId = productIdStr;
            book.image_url = image_urlStr;
            book.dd_sale_price = dd_sale_priceStr;
            book.original_price = original_priceStr;
            [YoungArray addObject:book];
            
        }
        NSLog(@"查询成功");
    }else
    {
        NSLog(@"登陆失败");
    }
    
    return YoungArray;
    
    
    
}


//清空数据库表
- (void)DropTable
{
    
    NSString *youngSql = @"DROP TABLE YoungTable";
    int result1 = sqlite3_exec(db, youngSql.UTF8String, NULL, NULL, nil);
    NSString *novelSql = @"DROP TABLE NovelTable";
    int result2 = sqlite3_exec(db, novelSql.UTF8String, NULL, NULL, nil);
    NSString *scienceSql = @"DROP TABLE ScienceTable";
    int result3 = sqlite3_exec(db, scienceSql.UTF8String, NULL, NULL, nil);
    NSString *socialSql = @"DROP TABLE SocialTable";
    int result4 = sqlite3_exec(db, socialSql.UTF8String, NULL, NULL, nil);
    NSString *managerSql = @"DROP TABLE ManagerTable";
    int result5 = sqlite3_exec(db, managerSql.UTF8String, NULL, NULL, nil);
    NSString *literatureSql = @"DROP TABLE LiteratureTable";
    int result6 = sqlite3_exec(db, literatureSql.UTF8String, NULL, NULL, nil);
    NSString *lifeSql = @"DROP TABLE LifeTable";
    int result7 = sqlite3_exec(db, lifeSql.UTF8String, NULL, NULL, nil);
    NSString *artSql = @"DROP TABLE ArtTable";
    int result8 = sqlite3_exec(db, artSql.UTF8String, NULL, NULL, nil);
    
    NSString *collectionSql = @"DROP TABLE CollectionTable";
    int result9 = sqlite3_exec(db, collectionSql.UTF8String, NULL, NULL, nil);
    
    NSString *allSql = @"DROP TABLE AllTable";
    int result10 = sqlite3_exec(db, allSql.UTF8String, NULL, NULL, nil);
    
    if (SQLITE_OK == result1) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    
    if (SQLITE_OK == result2) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    
    if (SQLITE_OK == result3) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    if (SQLITE_OK == result4) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    if (SQLITE_OK == result5) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    if (SQLITE_OK == result6) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    if (SQLITE_OK == result7) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    if (SQLITE_OK == result8) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    if (SQLITE_OK == result9) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    if (SQLITE_OK == result10) {
        NSLog(@"清除表成功");
    } else {
        NSLog(@"清除表失败");
    }
    
}

@end
